import pymysql


class DBUtil:
    # 定义类属性：conn
    conn = None

    @classmethod
    # 创建连接数据库的方法
    def __connect_db(cls):
        cls.conn = pymysql.connect(host="211.103.136.244", port=7061, user="student", password="iHRM_student_2022",
                                   database="ihrm", charset="utf8")
        return cls.conn

    # 关闭数据库连接的方法
    @classmethod
    def __close_db(cls):
        cls.conn.close()

    # 数据库的查询方法
    @classmethod
    def query_sql(cls, sql):
        cursor = None
        result = None
        try:
            # 创建数据库连接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行SQL，返回结果
            cursor.execute(sql)
            result = cursor.fetchone()
        except Exception as err:
            print("查询数据库报错：", str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_db()
            return result

    # 数据库的增删改查方法
    @classmethod
    def uid_sql(cls, sql):
        cursor = None
        affect_rows = None
        try:
            # 创建数据库连接
            cls.conn = cls.__connect_db()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行SQL,打印结果
            cursor.execute(sql)
            affect_rows = cls.conn.affected_rows()
            # 事务的提交、回滚
            cls.conn.commit()
        except Exception as err:
            print("操作数据库报错：", str(err))
            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_db()
        return affect_rows


if __name__ == '__main__':
    print(DBUtil.query_sql("SELECT * from t_book ;"))
    print(DBUtil.uid_sql("INSERT into t_book (id, title,pub_date) values(72, 'AG超玩会','1997-01-02');"))
